package com.ajr.core.visma;

import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Vector;

import org.apache.log4j.Logger;

import se.codevision.jinterlink.JInterLink;

import com.ajr.core.ArticleAction;
import com.ajr.core.ArticleObject;
import com.ajr.core.OrderDetails;
import com.ajr.core.OrderObject;
import com.ajr.core.OrderProductAttributes;
import com.ajr.core.Settings;
import com.ajr.core.OrderAction;
import com.ajr.util.MessageBundle;
import com.jnr.servlets.SubmitOrder;

public class OrderExport {
	Logger logger = Logger.getLogger(OrderExport.class);

	boolean customerInserOnly = false;
//	String message = null;
	
	public ExportResult exportOrder(String orderId,boolean insertOrder,boolean updateCustomer) throws Exception{
		
		OrderAction uord = new OrderAction();
		boolean failed = false;

//		message = "Nothing exported";
		CustomersAction cusotmerAction = new CustomersAction();
		ExportResult customerNumber = new ExportResult();
		customerNumber.setMessage(ExportResult.OSC_ORDER_EXPORT_NOTHING_CODE,MessageBundle.getMessage("order.not.exported"));
		int customerGroupShowTaxId = 0;
		int customerGroupId = 0;
		boolean customerExists = true;
		
		//if(insertCustomer) {
			customerNumber = cusotmerAction.getCustomerId(Integer.parseInt(orderId),updateCustomer);
			customerGroupShowTaxId = customerNumber.getCustomerGroupShowTaxId();
			customerGroupId = customerNumber.getCustomerGroupId();
			logger.info("customerGroupShowTaxId && "+customerGroupShowTaxId);
			logger.info("Customer add/update status "+customerNumber.isStatus());
			logger.info("Customer add/update message "+customerNumber.getMessage());
			customerExists = customerNumber.isCustomerExists();
			//customerNumber.setNumber(number)
//			message = customerNumber.getResultMessage();
			
			/*customerNumber = new ExportResult();
			customerNumber.setStatus(true);
			customerNumber.setCustomerNumber(String.valueOf(0));*/
			
			logger.info("message ** "+customerNumber.getResultMessage());
		//}
//		int exportedOrderNumber = 0;
		
		if(customerNumber.isStatus()) {
			if(insertOrder) {
				logger.info("Customer is available");
				// start order insert
				
				// Configuration configuration = new Configuration().configure();
				// SessionFactory sessionFactory =
				// configuration.buildSessionFactory();

				String url = Settings.getInstance().getConnectionURL();
				String user = Settings.getInstance().getUserName();
				String password = Settings.getInstance().getPassword();
				String driver = Settings.getInstance().getDriverClassName();
				
				Class.forName(driver);
//				DriverManager.getConnection(url, user, password);
				// Session session = sessionFactory.openSession();
				// java.sql.Connection c =
				// sessionFactory.openSession().connection();
				java.sql.Connection c = DriverManager.getConnection(url, user,password);

				String orderDetailSQL = "select * from orders_total where orders_id = "+orderId;
				
				Statement stmt1 = c.createStatement();
				ResultSet rs1 = stmt1.executeQuery(orderDetailSQL);
				OrderDetails orderDetails = new OrderDetails();
				while (rs1.next()) {
					String className = rs1.getString("class");
					double value = rs1.getDouble("value");
					double orders_total = rs1.getDouble("value");
					orderDetails.add(className, value);
				}
				
				logger.info("Order details for ["+orderId+"]"+orderDetails);
				
				rs1.close();
				stmt1.close();

				String sql = "select o.*,op.*,cu.* from orders o,orders_products op,currencies cu where "
//						+ "c.customers_id=o.customers_id and "
//						+ "pd.products_id = op.products_id and  "
						+ "op.orders_id = o.orders_id and  "
						+ "o.currency = cu.code and  "
						+ "op.orders_id ="
						+ orderId + " order by op.orders_products_id";
				logger.info("order detail query"+sql);
				// session.get
				// Query q = session.createSQLQuery(sql);
				Statement stmt = c.createStatement();
				ResultSet rs = stmt.executeQuery(sql);

				OrderObject orderObject = new OrderObject();
				orderObject.setOrderDetails(orderDetails);
				
				ResultSetMetaData rsmd = rs.getMetaData();
				
				Vector vector = new Vector();
				while (rs.next()) {
										
					ArticleObject articleObject = new ArticleObject();
					articleObject.setName(rs.getString("op.products_name"));
					articleObject.setNumber(rs
							.getString("op.products_model"));
					articleObject.setQuantity(rs.getDouble("op.products_quantity"));
					articleObject.setPrice(rs.getDouble("op.final_price"));
					articleObject.setTax(rs.getDouble("op.products_tax"));
					
					if(Settings.getInstance().isSuppliersPriceInclude()) {
						articleObject.setSupplierPrice(rs.getDouble("suppliers_final_price"));
					}else {
						logger.info("suppler price not includubg");
					}
					if(Settings.getInstance().isProductDiscountInclude()) {
						articleObject.setDiscount(rs.getDouble("op.products_discount"));
					}
					if(Settings.getInstance().isProductAccountsInclude()) {
						articleObject.setAccount(rs.getInt("op.account"));
						
					}
					if(Settings.getInstance().isEconomicInclude()) {
					articleObject.setEconomic_unit(rs.getString("op.economic_unit"));}
//					articleObject.setTax(rs.getDouble("op.products_tax"));
					if(Settings.getInstance().isProductsBatchInclude()) {
					articleObject.setProcutBatch(rs.getString("op.products_batch")); }
					articleObject.setDatePurchased(rs.getDate("date_purchased"));
					articleObject.setLastModified(rs.getDate("last_modified"));
					articleObject.setVatValue(rs.getDouble("op.products_tax"));
//					logger.info(rs.getInt("pd.language_id"));
//					articleObject.setLanguageId(rs.getInt("pd.language_id"));
					vector.add(articleObject);
					
					
					printValues(rs);

					orderObject.addArticleNumber(articleObject);
					orderObject.setCustomerNumber(customerNumber.getCustomerNumber());
					orderObject.setCustomerOrderNumber(Integer.parseInt(orderId));
					orderObject.setCurrency(rs.getString("cu.code"));
					orderObject.setCurrencyValue(rs.getDouble("cu.value"));
					orderObject.setPaymentMethod(rs.getString("o.payment_method"));
					if(Settings.getInstance().isAddBillingRefence()) {
					orderObject.setBillingReference(rs.getString("o.billing_reference")); }
					orderObject.setOrderDate(rs.getDate("o.date_purchased"));
					if(Settings.getInstance().isEconomicInclude()) {
					orderObject.setEconomic_unit(rs.getString("o.economic_unit"));}
					
					String orderAttributesSQL = "select * from orders_products_attributes where orders_id = "+orderId +" and orders_products_id = "+rs.getInt("op.orders_products_id");
					
					Statement stmt2 = c.createStatement();
					ResultSet rs2 = stmt2.executeQuery(orderAttributesSQL);
					OrderProductAttributes orderProductAttributes = new OrderProductAttributes();
					while (rs2.next()) {
						String className = rs2.getString("products_options");
						String value = rs2.getString("products_options_values");
//						double orders_total = rs2.getDouble("value");
						orderProductAttributes.add(className, value);
//						logger.info(className + ":"+value);
					}
					rs2.close();
					stmt2.close();
					articleObject.setOrderProductAttributes(orderProductAttributes);
					
					
				}
				rs.close();
				stmt.close();
				if(!failed) {
					ArticleAction articleAction = new ArticleAction();
					articleAction.insertArticle(vector);
					
					if(Settings.getInstance().getSocsExportType().equalsIgnoreCase("Invoice")) {
						customerNumber = uord.update(orderObject,customerGroupId,customerGroupShowTaxId,customerExists);
					}else if(Settings.getInstance().getSocsExportType().equalsIgnoreCase("order")){
						customerNumber = uord.updateAsOrder(orderObject,customerGroupId,customerGroupShowTaxId,customerExists);
					}
					
					logger.info("message ## "+customerNumber.getResultMessage());
					
					/*if(!customerNumber.isStatus()) {
						
					}*/
					
					logger.info("Products inserted "+orderObject.getAritcleObjectList().size());
					//message = "Successfully exported the order";				
					
				}
				
				// order inserted
				
				
				
			}
		}/*else {
			message = "Invalid Customer Details";
			
		}*/
		
		// List list = q.list();
		// logger.info("list size "+list.get(0).getClass());
		// session.close();
			
		
		return customerNumber;//exportedOrderNumber;
		
	}
	
	public void printValues(ResultSet rs) {

		try {
			java.sql.ResultSetMetaData rsmd = rs.getMetaData();
			int c = rsmd.getColumnCount();
			for (int i = 01; i <= c; i++) {
				try {
					logger.debug(rsmd.getColumnName(i) + ":"
							+ rs.getObject(i));

				} catch (Exception e) {
					// TODO: handle exception
				}

			}

		} catch (Exception e) {
			logger.error("Error while printing values", e);
		}

	}

	
	public static void main(String[] args) {
		String system = Settings.getInstance().getSystemPath();
		String company =  Settings.getInstance().getCompanyPath();
		String s =  Settings.getInstance().getLibraryPath();

		String message = "";

		JInterLink j = JInterLink.getInstance();
		try {

			j.open(s, system, company);
			// Article article = JInterLinkRegisterFactory.createArticle();
			
			VismaEnvironment v = VismaEnvironment.getInstance();
			System.out.println(v);

			String orderId = "719";//"7139";//"9208";
			orderId = "7139";
			orderId = "7140";
			orderId = "9295";
			orderId = "9292";
			orderId = "719";
			orderId = "10067";
			orderId = "10076";
			String customerInsert = "true";

			if (orderId != null && orderId.length() > 0) {
				OrderExport orderExport = new OrderExport();
				ExportResult expNumber = orderExport.exportOrder(orderId, true,true);
				message = expNumber.getResultMessage();
			}else {
				message = "Please provide valid OrderId";				
			}

			j.close();


		} catch (Exception e) {
			e.printStackTrace();
			// TODO: handle exception
		}
		
		System.out.println("message "+message);
		
	}

	

}
